/*
 * 在数据库中建立view
 * 
 * 
 */



package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import context.Context;

public class CreateViewDatabase {

	public static ResultSet getNewPayRecord(String quaryID) {
		Context c = Context.newInstance();

		Statement st = null;

		ResultSet rs = null;

		// ResultSetMetaData rsmd = null;

		//TODO: 修改tablename可实时变化
		String tableName = "networkpay";

		// String quaryID = "'5088010100000011771'";


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();

			String sqlCreateView = "use easipaybank; "
					+ "create view recharge as "
					+ "select cardID, tradeDate, tradeTime, amount from newpayrecord where tradeType ='Z3004';"
					+

					"create view onlinepay as "
					+ "select cardID, tradeDate, tradeTime, amount from newpayrecord where tradeType ='00F00900' or tradeType = '01C00120'; "
					+

					"create view pospay as "
					+ "select cardID, tradeDate, tradeTime, amount from newpayrecord where tradeType ='01C00121'; "
					+

					"create view onlinerecharge as "
					+ "select cardID, tradeDate, tradeTime, amount from newpayrecord where tradeType ='01C00420'; "
					+

					"create view swift as "
					+ "select cardID, tradeDate, tradeTime, amount from newpayrecord where tradeType ='01C02600'; ";

			String sqlCreateMergeView = "use easipaybank; "
					+ "create view merge_onlinepay as "
					+ "select tradeDate, sum(amount) from onlinepay group by tradeDate; "
					+

					"create view merge_onlinerecharge as "
					+ "select tradeDate, sum(amount) from recharge group by tradeDate; "
					+

					"create view merge_pospay as "
					+ "select tradeDate, sum(amount) from pospay group by tradeDate; "
					+

					"create view merge_recharge as "
					+ "select tradeDate, sum(amount) from recharge group by tradeDate; "
					+

					"create view merge_swift as "
					+ "select tradeDate, sum(amount) from swift group by tradeDate; ";



			rs = st.executeQuery(sqlCreateView);

			

			rs = st.executeQuery(sqlCreateMergeView);
			// rsmd = rs.getMetaData();

		} catch (Exception e) {

			e.printStackTrace();
		}


		return rs;
	}

	public static ResultSet getViewRecord(String viewName) {
		Context c = Context.newInstance();

		Statement st = null;

		ResultSet rs = null;


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();

			String sqlx = "select * from " + viewName;


			rs = st.executeQuery(sqlx);

		} catch (Exception e) {

			e.printStackTrace();
		}


		return rs;
	}
}
